from typing import Iterable, Union
from pymysql.connections import Connection
from pymysql.cursors import Cursor
import pymysql


class db_client:
    def __init__(self) -> None:
        self.conn: Union[Connection, None] = None

    def connect(
        self, host: str, user: str, password: str, port: int = 3306, db: str = ""
    ) -> Connection:
        return self.__conn(host, user, password, port, db)

    def close(self):
        if self.conn is not None:
            self.conn.close()

    @staticmethod
    def text(text: str) -> str:
        sql: str = "'" + text.replace("'", "''").replace("\\", "\\\\") + "'"
        return sql

    @staticmethod
    def date(text: str) -> str:
        if len(text) == 0:
            return "null"
        return "'" + text.replace("'", "''") + "'"

    def fetchall(self, sql: str, args: object = None) -> list:
        cursor: Cursor = self.conn.cursor()

        try:
            cursor.execute(sql, args)
            return cursor.fetchall()
        finally:
            cursor.close()

    def fetchone(self, sql: str, args: object = None) -> Union[tuple, None]:
        cursor: Cursor = self.conn.cursor()

        try:
            cursor.execute(sql, args)
            return cursor.fetchone()
        finally:
            cursor.close()

    def fetchscalar(self, sql: str, args: object = None) -> any:
        cursor: Cursor = self.conn.cursor()

        try:
            cursor.execute(sql, args)
            row = cursor.fetchone()
            if row == None:
                return None
            return row[0]
        finally:
            cursor.close()

    def execute(self, sql: str, args: object = None) -> int:
        cursor: Cursor = self.conn.cursor()

        try:
            affectedRows = cursor.execute(sql, args)
            self.conn.commit()
            return affectedRows
        finally:
            cursor.close()

    def executemany(self, sql: str, args: Iterable[object]) -> int:
        cursor: Cursor = self.conn.cursor()

        try:
            affectedRows = cursor.executemany(sql, args)
            self.conn.commit()
            return affectedRows
        finally:
            cursor.close()

    def insert(self, sql: str, args: object = None) -> int:
        cursor: Cursor = self.conn.cursor()

        try:
            _ = cursor.execute(sql, args)
            self.conn.commit()
            return cursor.lastrowid
        finally:
            cursor.close()

    def mogrify(self, sql: str, args: object = None) -> str:
        cursor: Cursor = self.conn.cursor()

        try:
            result = cursor.mogrify(sql, args)
            self.conn.commit()
            return result
        except Exception as e:
            self.conn.rollback()
            raise e
        finally:
            cursor.close()

    def __conn(
        self, host: str, user: str, password: str, port: int = 3306, db: str = ""
    ) -> Connection:
        self.conn = pymysql.connect(
            host=host,
            user=user,
            password=password,
            port=port,
            database=db,
        )
        return self.conn
